package com.example.flshtarvel.mapper;

import com.example.flshtarvel.entity.Route;
import com.example.flshtarvel.vo.RouteVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface RouteMapper {

    @Insert("insert into route(route_id,route_name,schedule,like_num,tag_id) values(#{routeId},#{routeName},#{schedule},#{likeNum},#{tagId}) ")
    public int insertRoute(RouteVo route);

    //@Select("select * from route where route_id=#{routeId}")
    @Select("select route.*,likeView.like_count,collectView.collect_count from route,(select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id and route.route_id=#{routeId}")
    public RouteVo selectRouteById(String routeId);

    //@Select("select * from route limit  #{offset},#{size} ")
    @Select("select route.*,likeView.like_count,collectView.collect_count from route,(select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id limit  #{offset},#{size} ")
    public List<RouteVo> selectAllRoute(int offset, int size);

    @Select("select count(*) from route")
    public int selectRouteCount();

    //@Select("select * from route where and route_name like #{likeSql}")
    @Select("select route.*,likeView.like_count,collectView.collect_count from route,(select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id and route.route_name like #{likeSql}")
    public List<RouteVo> selectRouteByName(String likeSql);

    @Update("update route set route_name=#{routeName},schedule=#{schedule},tag_id=#{tagId},like_num=0 where route_id=#{routeId}")
    public int updateRoute(Route route);

    @Delete("delete from route where route_id=#{routeId}")
    public int deleteRoute(String routeId);

    //查询所有路线    根据时间排序
    @Select("select route.*,likeView.like_count,collectView.collect_count from route,(select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id ORDER BY time DESC limit #{offset},#{size} ")
    public List<RouteVo> selectRouteByTime(int offset, int size);

    //查询所有路线    根据时间排序
    @Select("select route.*,likeView.like_count,collectView.collect_count from route,(select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id ORDER BY time ASC limit #{offset},#{size} ")
    public List<RouteVo> selectRouteByHot(int offset, int size);

}
